package com.wf.smartcomm.server.util;

import java.io.File;
import java.io.FileInputStream;
import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Calendar;
import java.util.HashMap;
import java.util.List;

import org.apache.commons.lang.StringUtils;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.xssf.usermodel.XSSFCell;
import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;


/**
 * 从EXCEL导入到数据库
 * 创建人：FH 创建时间：2014年12月23日
 * @version
 */
public class ObjectExcelRead {

	/**
	 * @param filepath //文件路径
	 * @param filename //文件名
	 * @param startrow //开始行号
	 * @param startcol //开始列号
	 * @param sheetnum //sheet
	 * @return list
	 */
	public static List<HashMap<String, String>> readExcel(String filepath, String filename, int startrow, int startcol, int sheetnum) {
		List<HashMap<String,String>> varList = new ArrayList<HashMap<String,String>>();

		try {
			File target = new File(filepath, filename);
			FileInputStream fi = new FileInputStream(target);
			//HSSFWorkbook wb = new HSSFWorkbook(fi);
			XSSFWorkbook xb = new XSSFWorkbook(fi);
			XSSFSheet sheet = xb.getSheetAt(sheetnum);
			//HSSFSheet sheet = xb.getSheetAt(sheetnum); 					//sheet 从0开始
			int rowNum = sheet.getLastRowNum() + 1; 					//取得最后一行的行号

			for (int i = startrow; i < rowNum; i++) {					//行循环开始
				
				HashMap<String,String> varpd = new HashMap<String,String>();
				//HSSFRow row = sheet.getRow(i);
				XSSFRow row = sheet.getRow(i);//行
				int cellNum = row.getLastCellNum(); 					//每行的最后一个单元格位置

				for (int j = startcol; j < cellNum; j++) {				//列循环开始
					
					XSSFCell cell = row.getCell(Short.parseShort(j + ""));
					String cellValue = null;
					if (null != cell) {
						if (j == 9 || j == 10) {
							 	double strCell = cell.getNumericCellValue();
							 	cellValue = doubledate2str(strCell+"");
						}else {
							cell.setCellType(Cell.CELL_TYPE_STRING);
							cellValue = cell.getStringCellValue();
						}
					} 
					varpd.put("var"+j, cellValue);
				}
				if (StringUtils.isNotEmpty(varpd.get("var0"))) {
					varList.add(varpd);
				}
			}

		} catch (Exception e) {
			System.out.println(e);
		}
		
		return varList;
	}
	
	public static String doubledate2str(String datestr) {


		String sss ="1900-01-01";


		SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");


		Calendar c = Calendar.getInstance();


		try {



		c.setTime(sdf.parse(sss));


		} catch (ParseException e) {



		e.printStackTrace();


		}


		String[] st = datestr.split("\\.");


		if (st.length == 2) {



		int i1 = Integer.parseInt(st[0]);



		String str ="0."+ st[1];



		long i2 = (long) (Double.parseDouble(str) * 24 * 3600);



		c.add(Calendar.DAY_OF_MONTH, i1);



		for (int i = 0; i < (i2 / Integer.MAX_VALUE); i++) {




		c.add(Calendar.SECOND, Integer.MAX_VALUE);



		}



		int iii = (int) (i2 % Integer.MAX_VALUE);



		c.add(Calendar.SECOND, iii);

		c.add(Calendar.DATE, -2);
		
		return String.valueOf(c.getTimeInMillis());


		}


		return"";

		}
}
